/*
    SQL语句
*/

/* **************************************
*   数据库相关 DDL
*************************************** */
// 1. 创建数据库
const CREATE_DB = dbName => `CREATE DATABASE IF NOT EXISTS ${dbName}`;


/* **************************************
*   数据表操作相关 DDL
*************************************** */
// 1. 查询所以数据表
const SHOW_ALL_TABLE = `SHOW TABLES`;
// 2. 新建表
const CREATE_TABLE = tableName => `CREATE TABLE IF NOT EXISTS ${tableName} (id int NOT NULL primary key AUTO_INCREMENT comment 'primary key',created_time TIMESTAMP COMMENT 'created time',updated_time TIMESTAMP COMMENT 'updated time')`;


/* **************************************
*   数据操作相关 DML
*************************************** */
// 1.查询表中的所有数据
const QUERYS_DATAS = tableName => `SELECT * FROM ${tableName}`;
// 2.插入数据 部分列
const INSERT_DATAS = (tableName, column, values) => `INSERT INTO ${tableName}(${column} VALUES (${values}))`;
// 3.删除数据
const DELETE_DATA_BY_ID = (tableName, id) => `DELETE FROM ${tableName} WHERE id = ${id}`;
// 4.删除所有数据
const DELETE_DATAS = tableName => `DELETE FROM ${tableName}`;
// 5.根据账号密码查询用户
const QUERY_USER_BY_USERNAME = (tableName, username, password) => `SELECT * FROM ${tableName} WHERE username = '${username}' AND password = '${password}'`;
// 6.根据uid查询用户详情
const QUERY_USER_DETAIL = uid => `SELECT * FROM userbase as ub INNER JOIN userdetail as ud ON ub.uid=ud.uid WHERE ud.uid=${uid}`
// 7.根据用户名和手机查询用户信息
const QUERY_USER_UID_BYUP = (username, phone) =>
    `
    SELECT ub.uid
    FROM userbase as ub
    JOIN userdetail as ud
    ON ub.uid=ud.uid
    WHERE ub.username='${username}'
    AND ud.phone='${phone}'
    `;
// 8.根据uid修改用户密码
const UPDATA_USER_PASSWORD_BYUID = (uid, password) =>
    `
    UPDATE userbase as ub
    SET ub.password= '${password}'
    WHERE ub.uid=${uid}
    `;
// 9.根据用户uid查询用户密码
const QUERY_USER_PASSWORD_BYUID = uid =>
    `
    SELECT ub.password
    FROM userbase as ub
    WHERE ub.uid=${uid}
    `;
// 10.




/*
    暴露SQL语句
*/
module.exports = {
    CREATE_DB,
    CREATE_TABLE,
    QUERYS_DATAS,
    INSERT_DATAS,
    DELETE_DATA_BY_ID,
    DELETE_DATAS,
    QUERY_USER_BY_USERNAME,
    QUERY_USER_DETAIL,
    QUERY_USER_UID_BYUP,
    UPDATA_USER_PASSWORD_BYUID,
    QUERY_USER_PASSWORD_BYUID
}

